﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Pms.Utils;
using System.Data.OracleClient;
using Biblioteca.Utils;
using Biblioteca.DataBase;

namespace SERIX.Classes
{
    public class CBensBeneficiarios
    {
        private string IDT_BENS_BENEF;
        private string IDT_BENEF;
        private string IDT_DESC;
        private string QUANT_BENEF;
        private string ANO_BENEF;
        private string TPO_VINC;
        private string BENS_BENEF;
        private string DATA_FIM;
        private string USER_FIM;
        private string IDT_EVOLUCAO;

        public CBensBeneficiarios()
        {

        }
        public CBensBeneficiarios(string IDT_BENS_BENEF, string IDT_BENEF, string IDT_DESC, string QUANT_BENEF, string ANO_BENEF, string TPO_VINC, string BENS_BENEF, string DATA_FIM, string USER_FIM)
        {
            this.IDT_BENS_BENEF = IDT_BENS_BENEF;
            this.IDT_BENEF = IDT_BENEF;
            this.IDT_DESC = IDT_DESC;
            this.QUANT_BENEF = QUANT_BENEF;
            this.ANO_BENEF = ANO_BENEF;
            this.TPO_VINC = TPO_VINC;
            this.BENS_BENEF = BENS_BENEF;
            this.DATA_FIM = DATA_FIM;
            this.USER_FIM = USER_FIM;
        }
        #region PROPRIEDADES
        
        public string Idt_Evolucao
        {
            get { return this.IDT_EVOLUCAO; }
            set { this.IDT_EVOLUCAO = value; }
        }

        /// <summary>
        /// Identificador da tabela Bens
        /// </summary>
        public string Idt_Bens_Benef
        {
            get
            {
                return this.IDT_BENS_BENEF;
            }
            set
            {
                this.IDT_BENS_BENEF = value;
            }
        }

        /// <summary>
        /// Identificador do Beneficiario
        /// </summary>
        public string Idt_benef
        {
            get
            {
                return this.IDT_BENEF;
            }
            set
            {
                this.IDT_BENEF = value;
            }
        }

        /// <summary>
        /// Identificador da Descrição do bem
        /// </summary>
        public string Idt_desc
        {
            get
            {
                return this.IDT_DESC;
            }
            set
            {
                this.IDT_DESC = value;
            }
        }

        /// <summary>
        /// Quantidade do Bem
        /// </summary>
        public string Quant_benef
        {
            get
            {
                return this.QUANT_BENEF;
            }
            set
            {
                this.QUANT_BENEF = value;
            }
        }

        /// <summary>
        /// Ano de inserção do bem
        /// </summary>
        public string Ano_benef
        {
            get
            {
                return this.ANO_BENEF;
            }
            set
            {
                this.ANO_BENEF = value;
            }
        }

        /// <summary>
        /// Tipo do Vinculo do Bem
        /// </summary>
        public string Tpo_vinc
        {
            get
            {
                return this.TPO_VINC;
            }
            set
            {
                this.TPO_VINC = value;
            }
        }

        public string Bens_benef
        {
            get
            {
                return this.BENS_BENEF;
            }
            set
            {
                this.BENS_BENEF = value;
            }
        }

        /// <summary>
        /// Data de ultima alteração
        /// </summary>
        public string Data_fim
        {
            get
            {
                return this.DATA_FIM;
            }
            set
            {
                this.DATA_FIM = value;
            }
        }

        /// <summary>
        /// Usuario que fez a ultima alteração
        /// </summary>
        public string User_fim
        {
            get
            {
                return this.USER_FIM;
            }
            set
            {
                this.USER_FIM = value;
            }
        }
#endregion

        public void Incluir(string Login, bool Evolucao)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            string _EVOL = "";
            string _CAMPO = "";
            string _CAMPO2 = "";
            if (Evolucao)
            {
                _EVOL = "_EVOL";
                _CAMPO = "IDT_EVOLUCAO, ";
                _CAMPO2 = IDT_EVOLUCAO + ", ";
            }
            string SQL = @"insert into Serix.SRX_BENS_BENEFICIARIO"+_EVOL+
			                  @"( "+_CAMPO+@"IDT_BENS_BENEF, IDT_BENEF, IDT_DESC, QUANT_BENEF, ANO_BENEF, TPO_VINC, BENS_BENEF, DATA_FIM, USER_FIM )
						   values
						      (" + _CAMPO2 + @":IDT_BENS_BENEF, :IDT_BENEF, :IDT_DESC, :QUANT_BENEF, :ANO_BENEF, :TPO_VINC, :BENS_BENEF, :DATA_FIM, :USER_FIM )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_BENS_BENEF", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("IDT_BENEF", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("QUANT_BENEF", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("ANO_BENEF", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("TPO_VINC", OracleType.Char));
                command.Parameters.Add(new OracleParameter("BENS_BENEF", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("DATA_FIM", OracleType.DateTime));
                command.Parameters.Add(new OracleParameter("USER_FIM", OracleType.VarChar));

                command.Parameters["IDT_BENS_BENEF"].Value = this.IDT_BENS_BENEF;
                command.Parameters["IDT_BENEF"].Value = this.IDT_BENEF;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                if (this.QUANT_BENEF == "")
                {
                    command.Parameters["QUANT_BENEF"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["QUANT_BENEF"].Value = this.QUANT_BENEF;
                }
                command.Parameters["ANO_BENEF"].Value = this.ANO_BENEF;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["BENS_BENEF"].Value = this.BENS_BENEF;
                if (this.DATA_FIM == "")
                {
                    command.Parameters["DATA_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DATA_FIM"].Value = this.DATA_FIM;
                }
                if (this.USER_FIM == "")
                {
                    command.Parameters["USER_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["USER_FIM"].Value = this.USER_FIM;
                }

                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
                command.Connection.Close();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();
                command.Connection.Close();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        public void Alterar(string Login, string Alteracao)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            string SQL = "";

            if (Alteracao == "Q")
            {
                SQL = @"update serix.SRX_BENS_BENEFICIARIO set
                            QUANT_BENEF = :QUANT_BENEF
                            WHERE ANO_BENEF = :ANO_BENEF AND IDT_DESC = :IDT_DESC AND IDT_BENEF = :IDT_BENEF";
            }
            else if (Alteracao == "D")
            {
                SQL = @"update serix.SRX_BENS_BENEFICIARIO set
                            DATA_FIM = :DATA_FIM, USER_FIM = :USER_FIM
                            WHERE ANO_BENEF = :ANO_BENEF AND IDT_DESC = :IDT_DESC AND IDT_BENEF = :IDT_BENEF";
            }
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;


                if (Alteracao == "Q")
                {
                    command.Parameters.Add(new OracleParameter("QUANT_BENEF", OracleType.VarChar));
                    if (this.QUANT_BENEF == "")
                    {
                        command.Parameters["QUANT_BENEF"].Value = DBNull.Value;
                    }
                    else
                    {
                        command.Parameters["QUANT_BENEF"].Value = this.QUANT_BENEF;
                    }
                }
                else if (Alteracao == "D")
                {
                    command.Parameters.Add(new OracleParameter("DATA_FIM", OracleType.DateTime));
                    command.Parameters.Add(new OracleParameter("USER_FIM", OracleType.VarChar));
                    command.Parameters["DATA_FIM"].Value = this.DATA_FIM;
                    command.Parameters["USER_FIM"].Value = this.USER_FIM;
                }               
                
                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));                
                command.Parameters.Add(new OracleParameter("ANO_BENEF", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("IDT_BENEF", OracleType.Number));
   
                command.Parameters["IDT_BENEF"].Value = this.IDT_BENEF;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;                
                command.Parameters["ANO_BENEF"].Value = this.ANO_BENEF;
                

                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
                command.Connection.Close();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();
                command.Connection.Close();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }            
        }

        static public DataTable PesquisarPorIdt(string Idt, string Login,bool Evolucao)
        {
            OracleCommand command = new OracleCommand();

            string SQL = "";
            if (!Evolucao)
            {
                SQL = @"select * from Serix.SRX_BENS_BENEFICIARIO WHERE IDT_BENEF = :IDT AND ANO_BENEF = :ANO_BENEF AND USER_FIM IS NULL";
                command.Parameters.Add(new OracleParameter("ANO_BENEF", OracleType.VarChar));
                command.Parameters["ANO_BENEF"].Value = DateTime.Now.Year;
            }
            else
            {
                SQL = @"select * from Serix.SRX_BENS_BENEFICIARIO_EVOL WHERE IDT_EVOLUCAO = :IDT";
            }
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT",OracleType.Number));
                command.Parameters["IDT"].Value = Idt;
                

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {               
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        static public DataTable ObterTodos(string Login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_BENS_BENEFICIARIO WHERE ANO_BENEF = :ANO_BENEF AND USER_FIM IS NULL";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("ANO_BENEF", OracleType.VarChar));
                command.Parameters["ANO_BENEF"].Value = DateTime.Now.Year.ToString();
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
                

            }
            catch (Exception e)
            {
                
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        static public string ObterIdt()
        {
            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string sql = "SELECT COUNT(IDT_BENS_BENEF) FROM SERIX.SRX_BENS_BENEFICIARIO";
            Query qy = new Query(con, sql);
            string retorno = "";

            try
            {
                con.Open();
                qy.Executar();
                retorno = Convert.ToString(int.Parse(qy.ObterCampo("COUNT(IDT_BENS_BENEF)")) + 1);
            }
            catch
            {

            }
            finally
            {
                con.Close();
            }
            return retorno;
        }

        static public CBensBeneficiarios PesquisarPorIdtDesc(string idt_desc, string Login)
        {
            CBensBeneficiarios obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_BENS_BENEFICIARIO 
                            WHERE IDT_DESC = :IDT_DESC AND ANO_BENEF = :ANO_BENEF AND USER_FIM IS NULL";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro("IDT_DESC", DbType.String, idt_desc);
                qy.AdicionarParametro("ANO_BENEF", DbType.String, DateTime.Now.Year.ToString());

                qy.Executar();
                obj = new CBensBeneficiarios(qy.ObterCampo("IDT_BENS_BENEF"), qy.ObterCampo("IDT_BENEF"), qy.ObterCampo("IDT_DESC"), qy.ObterCampo("QUANT_BENEF"), qy.ObterCampo("ANO_BENEF"), qy.ObterCampo("TPO_VINC"), qy.ObterCampo("BENS_BENEF"), qy.ObterCampo("DATA_FIM"), qy.ObterCampo("USER_FIM"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CBensBeneficiarios();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }


    }
}
